library(tidyverse)
library(readxl)
library(tidycensus)
library(openintro)
library(tm)
library(haven)

## Load data and keep needed variables
farmers1 = read_excel("2021-AMS-00030-F Final Response Document_Release.xlsx") %>%
  select(`...2`, `...4`, `...5`, `...8`, `...9`, `...11`, `...12`, `...13`) %>%
  rename(recipient = `...2`, type = `...4`, date = `...5`, state = `...8`, county = `...9`, zip = `...11`, boxes = `...12`, money = `...13`) 
farmers1 = farmers1[-1,]

farmers2 = read_excel("2022-AMS-00070-F Final Response Document_Release.xlsx") %>%
  select(`...2`, `...4`, `...5`, `...8`, `...9`, `...11`, `...12`, `...13`) %>%
  rename(recipient = `...2`, type = `...4`, date = `...5`, state = `...8`, county = `...9`, zip = `...11`, boxes = `...12`, money = `...13`) 
farmers2 = farmers2[-1,]

farmers = farmers1 %>%
  bind_rows(., farmers2) %>%
  select(boxes, county, recipient, state)

## Merge in organization types
types = read_excel("recipient_names.xlsx") %>%
  mutate(`non-profit` = case_when(pantry == 1 ~ 1,
                                  TRUE ~ `non-profit`)) %>%
  select(-pantry) %>%
  rename(non_profit = `non-profit`, church = `faith-based`)

farmers = farmers %>%
  left_join(., types, by = c("county", "recipient", "state")) %>%
  select(-county, -recipient, -state) %>%
  pivot_longer(!boxes, names_to = "recipient", values_to = "drop") %>%
  filter(drop == 1) %>%
  select(-drop)

boxes = farmers %>%
  mutate(boxes = as.numeric(boxes))

boxes = boxes %>%
  group_by(recipient) %>%
  summarise(total = sum(boxes, na.rm = T)) %>%
  mutate(pct = total/sum(total)*100)

## Plot 
recipients = ggplot(boxes, aes(x = reorder(as.factor(recipient), -pct), 
                                                            y = pct)) + 
  geom_bar(stat = "identity", position = "dodge") +
  geom_text(aes(label=
                  paste0(I(round(pct, digits = 2)), "%")), 
            position=position_dodge(width=0.9), vjust=-0.45, size = 2) +
  labs(x = "Recipient Type", y = "Share of Boxes") +
  scale_y_continuous(limits = c(0, 35), breaks = c(0, 10, 20, 30, 40),
                     labels = function(x) paste0(x, "%")) +
  scale_x_discrete(labels = c("Non-Profit", "Food Bank", "Church",
                              "School", "Govt.", "Hospital", "Other")) +
  theme_bw() +
  theme(panel.grid.major = element_blank(), 
        panel.grid.minor = element_blank(),
        panel.background = element_rect(colour = "black"),
        legend.position = "bottom")


